Shareware Super Platinum 8/Shareware Super Platinum 8.iso/mac/DATABASE/TECH91.ZIP;1/TI698.ASC

Text File | 1991-08-26 | 5.4 KB | 199 lines

  PRODUCT  :  Paradox                                NUMBER  :  698
  VERSION  :  3.5
       OS  :  DOS
     DATE  :  August 26, 1991                          PAGE  :  1/3

    TITLE  :  Conditional Fields in Forms and Reports in Paradox




  While the Paradox report generator when combined with queries has
  great flexibility and power, frequently the ability to do IF -
  THEN conditions in reports is desired.  To a limited degree the
  addition of logic to the report generator can be achieved through
  skillful use of some of the PAL functions: search(), substr() and
  len().

  With all the variations of the below listed formula which are
  possible, the key to achieving the desired result stems from
  thoroughly understanding the search and substring  functions.

  NOTE: Knowledge of PAL is a prerequisite for attempting the ideas
  contained in this document.

       Search - returns the position of one string within another

          syntax:  Search(substring,string)
                    where substring is an expression containing
                    the substring you want to locate within
                    String.

       examples:  Search("in","mine")                = 2
                     Search("ni","mine")             = 0
                     Search("in","mining")           = 2
                     (first occurrence)

                     Search(1,123456)                = 1
                     Search([fieldname],[fieldname]) = 1

       Substr - returns a subset or 'subtring' of a string

       syntax:  Substr("String",x,y)
                     where x=position of character to start at in
                     String and y=total number of characters to
                     extract.

       examples:  Substr("cat",2,2)                  = at
                     Substr("cat",0,2)               = blank
                     Substr("cat",2,0)               = blank
                     Substr("cat",1,255)             = cat

  Note that a "0" in either column of this function returns a














  PRODUCT  :  Paradox                                NUMBER  :  698
  VERSION  :  3.5
       OS  :  DOS
     DATE  :  August 26, 1991                          PAGE  :  2/3

    TITLE  :  Conditional Fields in Forms and Reports in Paradox




  blank.  By setting the Search function (which can be set to
  return a "0" or "1") equal to x in the substr function, one can
  make the display of "String" conditional.

       Len - returns the length of a string

       syntax:  Len("String")

       examples: Len("String")                       = 6
                 Len("cat")                          = 3


  Example 1:

  Substr("Text",search([fieldname]),a),255
        or more specifically,

  Substr("Dangerous Level",search([level],5),255)

  If the value for the field [level] in that record was
  equal to 5, then the Search function would equate to 1, thus
  displaying or printing the message "Dangerous Level".  If [level]
  is 0, then the text will not be printed.

  Example 2: Multi-conditional formula

  Substr("Dangerous Level",search([level],5),255) +
  Substr("Safe Level",search([level],1),255)

  This is simply example 1 concatenated with another substr
  formula checking against a value of 1.  Thus if [level]=5 then
  "Dangerous Level"+blank yields "Dangerous Level".  If [level]=5
  then blank+"Safe Level" = "Safe Level".  All other values of
  [level] will result in no text being printed in a report or
  displayed in a form.

  Example 3: Multi-conditional formula using the Len function

  Occasionally it may be desirable to print reports with labels
  where one of the fields may be empty.  Normally in Paradox this
  can result in an extra space being printed.  For example if the
  data includes a salutation field which may in some cases be














  PRODUCT  :  Paradox                                NUMBER  :  698
  VERSION  :  3.5
       OS  :  DOS
     DATE  :  August 26, 1991                          PAGE  :  3/3

    TITLE  :  Conditional Fields in Forms and Reports in Paradox




  empty, the report will output a blank space even if field squeeze
  is used; and a calculated field [Sal] + " " + [F] + " " + [L]
  will not correct this.  Unfortunately this is a scenario that
  neither fieldsqeeze nor a calculated field can adequately handle.

  The example below will remove the extra space encountered when
  the salutation field is empty, use the following expression in a
  calculated field.

   substr([Sal]+" "+[F]+" "+[L],1-search(len([Sal]),0),255)+
   substr([Sal]+[F]+" "+[L],search(len([Sal]),0),255)

  The entire expression must be typed on a single line.

  The string [Sal] represents the salutation field,
             [F]   represents the First name field, and
             [L]   represents the last name field.

  Substitute in your field names.

  These formulas can be placed in calculated fields in both
  reports and forms.  The limitations here are that the expressions
  can only be 175 characters in length.  There is a wide variety of
  combinations possible with the substr function given that there
  are three components to the expression that can be manipulated.
  Whenever this is explored, be sure to test for all possible
  values as it is very easy to come across limitations and
  unexpected results.